#include "database.h"

#include "common/ConstData.h"  // 包含 DEVICE_NAMES

void Database::initTable()
{
    QSqlQuery query;
    // 创建新表结构：时间戳、设备名称、测量值
    QString createTableSql =
        "CREATE TABLE IF NOT EXISTS device_readings ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "timestamp TEXT NOT NULL, "       // 时间戳
        "device_name TEXT NOT NULL, "     // 设备名称
        "value REAL, "                    // 测量值
        "UNIQUE(timestamp, device_name)"  // 确保每个时间点每个设备只有一个读数
        ")";

    // 创建索引以加速查询
    QString createIndexSql =
        "CREATE INDEX IF NOT EXISTS idx_readings_timestamp "
        "ON device_readings (timestamp)";

    // 创建核查记录表
    QString createInspectionTableSql =
        "CREATE TABLE IF NOT EXISTS device_inspection_records ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "device_name TEXT NOT NULL, "
        "standard_concentration REAL, "  // 标准浓度
        "measured_concentration REAL, "  // 测量浓度
        "measured_time TEXT NOT NULL, "  // 测量时间
        "record_type TEXT NOT NULL, "    // 'zero_check' 或 'std_check'
        "created_at TEXT, "
        "UNIQUE(device_name, measured_time, record_type)"  // 确保设备+时间+类型唯一
        ")";

    // 创建校准记录表
    QString createCalibrationTableSql =
        "CREATE TABLE IF NOT EXISTS device_calibration_records ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "device_name TEXT NOT NULL, "
        "calib_type TEXT NOT NULL, "        // 'standard_1' 或 'standard_2'
        "standard_concentration REAL, "     // 标准浓度
        "signal_value REAL, "               // 信号值
        "absorbance REAL, "                 // 吸光度值
        "calibration_time TEXT NOT NULL, "  // 校准时间
        "created_at TEXT, "
        "UNIQUE(device_name, calibration_time, calib_type)"  // 确保设备+时间+类型唯一
        ")";

    // 创建加标回收记录表
    QString createSpikeRecoveryTableSql =
        "CREATE TABLE IF NOT EXISTS device_spike_recovery_records ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "device_name TEXT NOT NULL, "
        "original_concentration REAL, "  // 原始水样浓度
        "spiked_concentration REAL, "    // 加标水样浓度
        "recovery_rate REAL, "           // 加标回收率
        "test_time TEXT NOT NULL, "      // 测试时间
        "created_at TEXT, "
        "UNIQUE(device_name, test_time)"  // 确保设备+时间唯一
        ")";

    // 创建核查记录索引
    QString createInspectionIndexSql =
        "CREATE INDEX IF NOT EXISTS idx_inspection_device_time "
        "ON device_inspection_records (device_name, measured_time)";

    // 创建校准记录索引
    QString createCalibrationIndexSql =
        "CREATE INDEX IF NOT EXISTS idx_calibration_device_time "
        "ON device_calibration_records (device_name, calibration_time)";

    // 创建加标回收记录索引
    QString createSpikeRecoveryIndexSql =
        "CREATE INDEX IF NOT EXISTS idx_spike_recovery_device_time "
        "ON device_spike_recovery_records (device_name, test_time)";

    if (!query.exec(createTableSql) || !query.exec(createIndexSql) ||
        !query.exec(createInspectionTableSql) || !query.exec(createCalibrationTableSql) ||
        !query.exec(createSpikeRecoveryTableSql) || !query.exec(createInspectionIndexSql) ||
        !query.exec(createCalibrationIndexSql) || !query.exec(createSpikeRecoveryIndexSql)) {
        qWarning() << "数据库初始化失败：" << query.lastError().text();
    }
}

QVector<QVariantMap> Database::queryByDate(const QDate &date)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    // 准备设备名称的SQL部分
    QStringList deviceCases;
    for (const QString &name : DEVICE_NAMES) {
        if (DEVICE_MAP[name].isShowData) {
            QString escapedName = name;
            escapedName.replace("'", "''");  // 转义设备名中的单引号
            deviceCases << QString(
                               "MAX(CASE WHEN device_name = '%1' THEN value ELSE NULL END) AS '%1'")
                               .arg(escapedName);
        }
    }

    // SQL查询：按时间戳分组，将每个设备的值作为单独的列
    QString sql = QString(
                      "SELECT timestamp, %1 "
                      "FROM device_readings "
                      "WHERE 1=1")
                      .arg(deviceCases.join(", "));

    // 添加日期过滤条件
    if (date.isValid()) {
        QString dateStr = date.toString("yyyy-MM-dd");
        sql += " AND date(timestamp) = '" + dateStr + "'";
    }

    sql +=
        " GROUP BY timestamp "
        "ORDER BY timestamp DESC";  // 最新时间在前

    if (query.exec(sql)) {
        while (query.next()) {
            QVariantMap record;
            record["timestamp"] = query.value("timestamp");
            for (const QString &name : DEVICE_NAMES) {
                if (DEVICE_MAP[name].isShowData) {
                    record[name] = query.value(name);
                }
            }
            results.append(record);
        }
    } else {
        qWarning() << "查询失败：" << query.lastError().text() << "SQL:" << sql;
    }

    return results;
}

int Database::getTotalRecords(const QDate &date)
{
    QSqlQuery query;
    int count = 0;

    // 计算不同时间戳的总数（每个时间点算一条记录）
    QString sql = "SELECT COUNT(DISTINCT timestamp) FROM device_readings WHERE 1=1";
    if (date.isValid()) {
        QString dateStr = date.toString("yyyy-MM-dd");
        sql += " AND date(timestamp) = '" + dateStr + "'";
    }

    if (query.exec(sql) && query.next()) {
        count = query.value(0).toInt();
    } else {
        qWarning() << "获取总记录数失败：" << query.lastError().text() << "SQL:" << sql;
    }

    return count;
}

QVector<QVariantMap> Database::queryByPage(int page, int pageSize, const QDate &date)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    // 计算偏移量
    int offset = (page - 1) * pageSize;

    // 准备设备名称的SQL部分
    QStringList deviceCases;
    for (const QString &name : DEVICE_NAMES) {
        if (DEVICE_MAP[name].isShowData) {
            QString escapedName = name;
            escapedName.replace("'", "''");  // 转义设备名中的单引号
            deviceCases << QString(
                               "MAX(CASE WHEN device_name = '%1' THEN value ELSE NULL END) AS '%1'")
                               .arg(escapedName);
        }
    }

    // 分页查询：先获取要显示的时间戳
    QString timestampSql = QString("SELECT DISTINCT timestamp FROM device_readings WHERE 1=1");

    if (date.isValid()) {
        QString dateStr = date.toString("yyyy-MM-dd");
        timestampSql += " AND date(timestamp) = '" + dateStr + "'";
    }

    timestampSql += " ORDER BY timestamp DESC LIMIT " + QString::number(pageSize) + " OFFSET " +
                    QString::number(offset);

    // 主查询：根据已选时间戳获取数据
    QString sql = QString(
                      "SELECT timestamp, %1 "
                      "FROM device_readings "
                      "WHERE timestamp IN (SELECT timestamp FROM (%2)) "
                      "GROUP BY timestamp "
                      "ORDER BY timestamp DESC")
                      .arg(deviceCases.join(", "))
                      .arg(timestampSql);

    if (query.exec(sql)) {
        while (query.next()) {
            QVariantMap record;
            record["timestamp"] = query.value("timestamp");
            for (const QString &name : DEVICE_NAMES) {
                if (DEVICE_MAP[name].isShowData) {
                    record[name] = query.value(name);
                }
            }
            results.append(record);
        }
    } else {
        qWarning() << "分页查询失败：" << query.lastError().text() << "SQL:" << sql;
    }

    return results;
}

bool Database::insertHourlyData(const QDateTime &timestamp,
                                const QMap<QString, double> &deviceValues)
{
    // 自动将任意时间转换为整点时间（保留年月日小时，分钟秒设为0）
    QDateTime hourlyTimestamp = timestamp;
    hourlyTimestamp.setTime(QTime(timestamp.time().hour(), 0, 0));

    // 格式化时间戳为字符串
    QString timestampStr = hourlyTimestamp.toString("yyyy-MM-dd HH:00:00");

    QSqlQuery query;
    bool success = true;

    // 开始事务
    QSqlDatabase::database().transaction();

    try {
        // 准备插入语句
        query.prepare(
            "INSERT OR REPLACE INTO device_readings (timestamp, device_name, value) "
            "VALUES (?, ?, ?)");

        // 遍历设备数据
        QMapIterator<QString, double> i(deviceValues);
        while (i.hasNext()) {
            i.next();
            // 仅处理在DEVICE_MAP中定义且显示数据的设备
            if (DEVICE_MAP.contains(i.key()) && DEVICE_MAP[i.key()].isShowData) {
                query.bindValue(0, timestampStr);
                query.bindValue(1, i.key());
                query.bindValue(2, i.value());

                if (!query.exec()) {
                    qWarning() << "插入数据失败：" << query.lastError().text() << "设备:" << i.key()
                               << "值:" << i.value();
                    success = false;
                    break;
                }
            }
        }

        if (success) {
            QSqlDatabase::database().commit();
            return true;
        } else {
            QSqlDatabase::database().rollback();
            return false;
        }
    } catch (...) {
        QSqlDatabase::database().rollback();
        qWarning() << "插入数据异常，已回滚";
        return false;
    }
}

bool Database::insertInspectionRecord(const QString &deviceName, const QString &recordType,
                                      double standardConcentration, double measuredConcentration,
                                      const QDateTime &measuredTime)
{
    QSqlQuery query;
    query.prepare(
        "INSERT INTO device_inspection_records "
        "(device_name, record_type, standard_concentration, "
        "measured_concentration, measured_time, created_at) "
        "VALUES (?, ?, ?, ?, ?, ?)");

    query.bindValue(0, deviceName);
    query.bindValue(1, recordType);
    query.bindValue(2, standardConcentration);
    query.bindValue(3, measuredConcentration);
    query.bindValue(4, measuredTime.toString("yy-MM-dd HH:mm:ss"));
    query.bindValue(5, QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));

    if (!query.exec()) {
        qWarning() << "插入核查记录失败：" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::insertCalibrationRecord(const QString &deviceName, const QString &calibType,
                                       double standardConcentration, double signalValue,
                                       const QDateTime &calibrationTime, double absorbance)
{
    QSqlQuery query;
    query.prepare(
        "INSERT INTO device_calibration_records "
        "(device_name, calib_type, standard_concentration, "
        "signal_value, absorbance, calibration_time, created_at) "
        "VALUES (?, ?, ?, ?, ?, ?, ?)");

    query.bindValue(0, deviceName);
    query.bindValue(1, calibType);
    query.bindValue(2, standardConcentration);
    query.bindValue(3, signalValue);
    query.bindValue(4, absorbance);
    query.bindValue(5, calibrationTime.toString("yy-MM-dd HH:mm:ss"));
    query.bindValue(6, QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));

    if (!query.exec()) {
        qWarning() << "插入校准记录失败：" << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::insertSpikeRecoveryRecord(const QString &deviceName, double originalConcentration,
                                         double spikedConcentration, double recoveryRate,
                                         const QDateTime &testTime)
{
    QSqlQuery query;
    query.prepare(
        "INSERT OR REPLACE INTO device_spike_recovery_records "
        "(device_name, original_concentration, spiked_concentration, recovery_rate, test_time, "
        "created_at) "
        "VALUES (?, ?, ?, ?, ?, ?)");

    query.bindValue(0, deviceName);
    query.bindValue(1, originalConcentration);
    query.bindValue(2, spikedConcentration);
    query.bindValue(3, recoveryRate);
    query.bindValue(4, testTime.toString("yyyy-MM-dd hh:mm:ss"));
    query.bindValue(5, QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));

    bool success = query.exec();
    if (!success) {
        qWarning() << "插入加标回收记录失败：" << query.lastError().text();
    }

    return success;
}

QVector<QVariantMap> Database::queryInspectionRecords(const QDate &date, const QString &deviceName)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    QString sql = "SELECT * FROM device_inspection_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(measured_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    sql += " ORDER BY measured_time DESC";

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex, deviceName);
    }

    if (query.exec()) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_name"] = query.value("device_name");
            record["record_type"] = query.value("record_type");
            record["standard_concentration"] = query.value("standard_concentration");
            record["measured_concentration"] = query.value("measured_concentration");
            record["measured_time"] = query.value("measured_time");
            record["created_at"] = query.value("created_at");
            results.append(record);
        }
    } else {
        qWarning() << "查询核查记录失败：" << query.lastError().text();
    }

    return results;
}

QVector<QVariantMap> Database::queryInspectionRecordsByPage(int page, int pageSize,
                                                            const QDate &date,
                                                            const QString &deviceName)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    int offset = (page - 1) * pageSize;

    QString sql = "SELECT * FROM device_inspection_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(measured_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    sql += " ORDER BY measured_time DESC LIMIT ? OFFSET ?";

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex++, deviceName);
    }
    query.bindValue(bindIndex++, pageSize);
    query.bindValue(bindIndex, offset);

    if (query.exec()) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_name"] = query.value("device_name");
            record["record_type"] = query.value("record_type");
            record["standard_concentration"] = query.value("standard_concentration");
            record["measured_concentration"] = query.value("measured_concentration");
            record["measured_time"] = query.value("measured_time");
            record["created_at"] = query.value("created_at");
            results.append(record);
        }
    } else {
        qWarning() << "分页查询核查记录失败：" << query.lastError().text();
    }

    return results;
}

int Database::getInspectionRecordsCount(const QDate &date, const QString &deviceName)
{
    QSqlQuery query;
    int count = 0;

    QString sql = "SELECT COUNT(*) FROM device_inspection_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(measured_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex, deviceName);
    }

    if (query.exec() && query.next()) {
        count = query.value(0).toInt();
    } else {
        qWarning() << "获取核查记录总数失败：" << query.lastError().text();
    }

    return count;
}

QVector<QVariantMap> Database::queryCalibrationRecords(const QDate &date, const QString &deviceName)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    QString sql = "SELECT * FROM device_calibration_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(calibration_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    sql += " ORDER BY calibration_time DESC";

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex, deviceName);
    }

    if (query.exec()) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_name"] = query.value("device_name");
            record["calib_type"] = query.value("calib_type");
            record["standard_concentration"] = query.value("standard_concentration");
            record["signal_value"] = query.value("signal_value");
            record["absorbance"] = query.value("absorbance");
            record["calibration_time"] = query.value("calibration_time");
            record["created_at"] = query.value("created_at");
            results.append(record);
        }
    } else {
        qWarning() << "查询校准记录失败：" << query.lastError().text();
    }

    return results;
}

QVector<QVariantMap> Database::queryCalibrationRecordsByPage(int page, int pageSize,
                                                             const QDate &date,
                                                             const QString &deviceName)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    int offset = (page - 1) * pageSize;

    QString sql = "SELECT * FROM device_calibration_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(calibration_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    sql += " ORDER BY calibration_time DESC LIMIT ? OFFSET ?";

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex++, deviceName);
    }
    query.bindValue(bindIndex++, pageSize);
    query.bindValue(bindIndex, offset);

    if (query.exec()) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_name"] = query.value("device_name");
            record["calib_type"] = query.value("calib_type");
            record["standard_concentration"] = query.value("standard_concentration");
            record["signal_value"] = query.value("signal_value");
            record["absorbance"] = query.value("absorbance");
            record["calibration_time"] = query.value("calibration_time");
            record["created_at"] = query.value("created_at");
            results.append(record);
        }
    } else {
        qWarning() << "分页查询校准记录失败：" << query.lastError().text();
    }

    return results;
}

int Database::getCalibrationRecordsCount(const QDate &date, const QString &deviceName)
{
    QSqlQuery query;
    int count = 0;

    QString sql = "SELECT COUNT(*) FROM device_calibration_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(calibration_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex, deviceName);
    }

    if (query.exec() && query.next()) {
        count = query.value(0).toInt();
    } else {
        qWarning() << "获取校准记录总数失败：" << query.lastError().text();
    }

    return count;
}

QVector<QVariantMap> Database::querySpikeRecoveryRecords(const QDate &date,
                                                         const QString &deviceName)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    QString sql = "SELECT * FROM device_spike_recovery_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(test_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    sql += " ORDER BY test_time DESC";

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex, deviceName);
    }

    if (query.exec()) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_name"] = query.value("device_name");
            record["original_concentration"] = query.value("original_concentration");
            record["spiked_concentration"] = query.value("spiked_concentration");
            record["recovery_rate"] = query.value("recovery_rate");
            record["test_time"] = query.value("test_time");
            record["created_at"] = query.value("created_at");
            results.append(record);
        }
    } else {
        qWarning() << "查询加标回收记录失败：" << query.lastError().text();
    }

    return results;
}

QVector<QVariantMap> Database::querySpikeRecoveryRecordsByPage(int page, int pageSize,
                                                               const QDate &date,
                                                               const QString &deviceName)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    QString sql = "SELECT * FROM device_spike_recovery_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(test_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    sql += " ORDER BY test_time DESC LIMIT ? OFFSET ?";

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex++, deviceName);
    }
    query.bindValue(bindIndex++, pageSize);
    query.bindValue(bindIndex, (page - 1) * pageSize);

    if (query.exec()) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_name"] = query.value("device_name");
            record["original_concentration"] = query.value("original_concentration");
            record["spiked_concentration"] = query.value("spiked_concentration");
            record["recovery_rate"] = query.value("recovery_rate");
            record["test_time"] = query.value("test_time");
            record["created_at"] = query.value("created_at");
            results.append(record);
        }
    } else {
        qWarning() << "分页查询加标回收记录失败：" << query.lastError().text();
    }

    return results;
}

int Database::getSpikeRecoveryRecordsCount(const QDate &date, const QString &deviceName)
{
    QSqlQuery query;
    int count = 0;

    QString sql = "SELECT COUNT(*) FROM device_spike_recovery_records WHERE 1=1";

    if (date.isValid()) {
        sql += " AND date(test_time) = ?";
    }
    if (!deviceName.isEmpty()) {
        sql += " AND device_name = ?";
    }

    query.prepare(sql);

    int bindIndex = 0;
    if (date.isValid()) {
        query.bindValue(bindIndex++, date.toString("yyyy-MM-dd"));
    }
    if (!deviceName.isEmpty()) {
        query.bindValue(bindIndex, deviceName);
    }

    if (query.exec() && query.next()) {
        count = query.value(0).toInt();
    } else {
        qWarning() << "获取加标回收记录总数失败：" << query.lastError().text();
    }

    return count;
}